-- =====================================================

CREATE OR REPLACE PACKAGE BODY ZOEDEVOPS.ZOEPKG_SECURITY 

-- Created in 2019.06.18 by polarbears
-- Copyright (c) 20xx, CHINA and/or affiliates.
-- All rights reserved.
--	Name:
-- 		zoepkg_security.pkb
--	Description:
-- 		基础安全包
--  Relation:
--      zoeUtility
--	Notes:
--		
--	修改 - （年-月-日） - 描述
--

AS

  FUNCTION ENCRYPT_DES(iv_text VARCHAR2, iv_key VARCHAR2, iv_db_id IN VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2
  AS
    -- 错误信息参数
    ln_return       NUMBER;
    --
    lv_crypto_key       VARCHAR2(64);
    lv_encrypted_text    VARCHAR2(1024);
    lrw_encrypt_text     RAW(2000) ;
    lrw_encrypt_key      RAW(32) ;
    lrw_encrypted_text   RAW(2000);
    lpi_encryption_type    PLS_INTEGER :=          -- total encryption type
                            DBMS_CRYPTO.ENCRYPT_AES256
                          + DBMS_CRYPTO.CHAIN_CBC
                          + DBMS_CRYPTO.PAD_PKCS5;
  BEGIN
    lrw_encrypt_text := UTL_I18N.STRING_TO_RAW (iv_text,  'AL32UTF8');
    IF iv_db_id IS NULL THEN 
        lv_crypto_key   := ZOEDEVOPS.ZOEFUN_CRYPTO_KEY();
        lrw_encrypt_key  := UTL_I18N.STRING_TO_RAW (lv_crypto_key||iv_key,  'AL32UTF8');
    ELSE
        lv_crypto_key   := ZOEDEVOPS.ZOEFUN_CRYPTO_KEY(iv_db_id);
        lrw_encrypt_key  := UTL_I18N.STRING_TO_RAW (lv_crypto_key||iv_key,  'AL32UTF8');
    END IF;
    IF lv_crypto_key = '-1' THEN
        RETURN '非法密钥';
    END IF;
    lrw_encrypted_text := DBMS_CRYPTO.ENCRYPT
      (
         src => lrw_encrypt_text,
         typ => lpi_encryption_type,
         key => lrw_encrypt_key
      );
    lv_encrypted_text := rawtohex(lrw_encrypted_text);
    --lv_encrypted_text :=  UTL_RAW.CAST_TO_VARCHAR2(lrw_encrypted_text);
    RETURN lv_encrypted_text;
  EXCEPTION 
    WHEN OTHERS THEN
        ln_return := ZOEDEVOPS.ZOEFUN_WERROR_LOG(
            ZOEDEVOPS.ZOEFUN_WERROR_JSON(
                SYS_CONTEXT('ZOE_DEVOPS_CONTEXT','ERROR_PROGRAM')||
                ','||SQLERRM||','||
                TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'))
            );
        RETURN '非法加密';
  END ENCRYPT_DES;

  FUNCTION DECRYPT_DES(iv_encrypted_text VARCHAR2, iv_key VARCHAR2, iv_db_id IN VARCHAR2 DEFAULT NULL)
    RETURN VARCHAR2
  IS
    -- 错误信息参数
    ln_return       NUMBER;
    --
    lv_crypto_key       VARCHAR2(64);
    lv_decrypted_text VARCHAR2(256);
    lrw_encrypt_text     RAW(256) ;
    lrw_encrypt_key      RAW(256) ;
    lrw_decrypted_text   RAW(2000);
    lpi_encryption_type    PLS_INTEGER :=          -- total encryption type
                            DBMS_CRYPTO.ENCRYPT_AES256
                          + DBMS_CRYPTO.CHAIN_CBC
                          + DBMS_CRYPTO.PAD_PKCS5;
 BEGIN
    ZOEDEVOPS.ZOEPRC_SET_DEVOPS_CONTEXT('ERROR_PROGRAM','ZOEDEVOPS.ZOEPKG_SECURITY.DECRYPT_DES');
    IF iv_db_id IS NULL THEN 
        lv_crypto_key   := ZOEDEVOPS.ZOEFUN_CRYPTO_KEY();
        lrw_encrypt_key  := UTL_I18N.STRING_TO_RAW (lv_crypto_key||iv_key,  'AL32UTF8');
    ELSE
        lv_crypto_key   := ZOEDEVOPS.ZOEFUN_CRYPTO_KEY(iv_db_id);
        lrw_encrypt_key  := UTL_I18N.STRING_TO_RAW (lv_crypto_key||iv_key,  'AL32UTF8');
    END IF;
    IF lv_crypto_key = '-1' THEN
        RETURN '非法密钥';
    END IF;
    lrw_encrypt_text := hextoraw(iv_encrypted_text);
    lrw_decrypted_text := DBMS_CRYPTO.DECRYPT
      (
         src => lrw_encrypt_text,
         typ => lpi_encryption_type,
         key => lrw_encrypt_key
      );
    lv_decrypted_text :=  UTL_I18N.RAW_TO_CHAR (lrw_decrypted_text, 'AL32UTF8');
    RETURN lv_decrypted_text;
  EXCEPTION 
    WHEN OTHERS THEN
        ln_return := ZOEDEVOPS.ZOEFUN_WERROR_LOG(
            ZOEDEVOPS.ZOEFUN_WERROR_JSON(
                SYS_CONTEXT('ZOE_DEVOPS_CONTEXT','ERROR_PROGRAM')||
                ','||SQLERRM||','||
                TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'))
            );
        RETURN '非法解密';
  END DECRYPT_DES;
  
  FUNCTION VERIFY_SH1(iv_text IN VARCHAR2)
    RETURN VARCHAR2
  AS
    lv_key     VARCHAR2(128);
    lv_text    VARCHAR2(32767);
    lrw_text   RAW(32767);
    lv_verify_text VARCHAR2(128);
  BEGIN
    lv_key     := 'KK3JZS70RM1L3FDDROBSIEH0VK6DMMGH151MKU0HKEN71OXQC28I96WF49WMFOHS';
    lv_text    := iv_text || lv_key;
    lrw_text   := SYS.UTL_I18N.STRING_TO_RAW(lv_text, 'AL32UTF8');
    lv_verify_text := dbms_crypto.HASH(lrw_text, dbms_crypto.HASH_SH1);
    RETURN lv_verify_text;
  END VERIFY_SH1;  

PROCEDURE GENERATE_KEY(iv_key VARCHAR2)
AS
    -- 错误信息参数
    ln_return       NUMBER;
    lv_sqlerrm      VARCHAR2(512);
    lv_error_date   VARCHAR2(64);
    lv_error_info_json    VARCHAR2(4000);
    --  用户信息参数
    lv_db_id    VARCHAR2(128);
    lv_key_type VARCHAR2(128);
    lv_creator  VARCHAR2(128);
    lv_sql      VARCHAR2(4000);
BEGIN
    ZOEDEVOPS.ZOEPRC_SET_DEVOPS_CONTEXT('ERROR_PROGRAM','ZOEDEVOPS.ZOEPKG_SECURITY.GENERATE_KEY');
    SELECT JSON_VALUE(iv_key,'$.DB_ID'),
        JSON_VALUE(iv_key,'$.KEY_TYPE'),
        JSON_VALUE(iv_key,'$.CREATOR')
    INTO lv_db_id,lv_key_type,lv_creator
    FROM DUAL;
    IF lv_key_type = 'RANDOM_KEY' THEN
        IF lv_db_id IS NULL THEN
            lv_sql := 'INSERT INTO ZOEDEVOPS.DVP_PROJECT_SECRET_KEY'; 
            lv_sql := lv_sql||' (KEY_ID#,KEY_TYPE,KEY_VALUE,CREATOR_CODE,CREATED_TIME,EXTEND_KEY)';
            lv_sql := lv_sql||' SELECT DB_ID,''RANDOM_KEY'',';
            lv_sql := lv_sql||' DBMS_RANDOM.STRING(''X'',16)';
            lv_sql := lv_sql||' ,'''||lv_creator||''',SYSDATE,';
            lv_sql := lv_sql||' DBMS_RANDOM.STRING(''X'',16)';
            lv_sql := lv_sql||' FROM ZOEDEVOPS.DVP_PROJ_DB_BASIC_INFO';
        ELSE
            lv_sql := 'INSERT INTO ZOEDEVOPS.DVP_PROJECT_SECRET_KEY'; 
            lv_sql := lv_sql||' (KEY_ID#,KEY_TYPE,KEY_VALUE,CREATOR_CODE,CREATED_TIME,EXTEND_KEY)';
            lv_sql := lv_sql||' SELECT DB_ID,''RANDOM_KEY'',';
            lv_sql := lv_sql||' DBMS_RANDOM.STRING(''X'',16)';
            lv_sql := lv_sql||' ,'''||lv_creator||''',SYSDATE,';
            lv_sql := lv_sql||' DBMS_RANDOM.STRING(''X'',16)';
            lv_sql := lv_sql||' FROM ZOEDEVOPS.DVP_PROJ_DB_BASIC_INFO';
            lv_sql := lv_sql||' WHERE DB_ID='''||lv_db_id||'''';
        END IF;
        EXECUTE IMMEDIATE lv_sql;
        COMMIT;
    END IF;
    EXCEPTION
        WHEN OTHERS THEN
            ln_return := ZOEDEVOPS.ZOEFUN_WERROR_LOG(
                ZOEDEVOPS.ZOEFUN_WERROR_JSON(
                    SYS_CONTEXT('ZOE_DEVOPS_CONTEXT','DB_ID')||
                    ','||SQLERRM||','||
                    TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS'))
                );
END GENERATE_KEY;

END ZOEPKG_SECURITY;
/

